package com.ihr360.excel;

import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.google.common.collect.Sets;
import com.ihr360.excel.annotation.ExcelCell;
import com.ihr360.excel.annotation.ExcelConfig;
import com.ihr360.excel.cellstyle.ExcelCellStyle;
import com.ihr360.excel.config.ExcelConfigurer;
import com.ihr360.excel.config.Ihr360TemplateExcelConfiguration;
import com.ihr360.excel.context.ExcelContext;
import com.ihr360.excel.entity.CellComment;
import com.ihr360.excel.entity.ExcelSheet;
import com.ihr360.excel.entity.ExportParams;
import com.ihr360.excel.entity.ImportParams;
import com.ihr360.excel.exception.ExcelException;
import com.ihr360.excel.logs.ExcelCommonLog;
import com.ihr360.excel.logs.ExcelLogItem;
import com.ihr360.excel.logs.ExcelLogType;
import com.ihr360.excel.logs.ExcelLogs;
import com.ihr360.excel.logs.ExcelRowLog;
import com.ihr360.excel.specification.CommonSpecification;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.collections.MapUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.BufferedInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.stream.Collectors;

import static com.ihr360.excel.handler.DataHandler.convertRowToHeaderMap;
import static com.ihr360.excel.handler.DataHandler.getSheetFromWorkbook;
import static com.ihr360.excel.handler.DataHandler.handleCommonSpecification;
import static com.ihr360.excel.handler.DataHandler.handleExcelRowToMap;
import static com.ihr360.excel.handler.DataHandler.handleImportExcelRowToJavabean;
import static com.ihr360.excel.handler.DataHandler.write2Sheet;
import static com.ihr360.excel.handler.ValidatorHandler.judgeHeader;
import static com.ihr360.excel.util.CommonExcelUtil.checkBlankRow;
import static com.ihr360.excel.util.CommonExcelUtil.isNullOrBlankStringCell;


/**
 * The <code>ExcelUtil</code> 与 {@link ExcelCell}搭配使用
 *
 * @author richey.liu
 * @version 1.0, Created at 2017-12-17
 */
public class ExcelUtil {

    private static Logger logger = LoggerFactory.getLogger(ExcelUtil.class);

    private ExcelUtil() {

    }


    /**
     * 单个sheet导出
     * 导出数据可以是：
     * 1.javabean类型的对象集合
     * 2.Map类型的对象集合
     * 3.表头顺序由Map的key顺序决定
     * 4.如果导出javabean类型对象，数据顺序由@ExcellCell注解的index属性决定
     *
     * @param <T>
     * @param out 与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中
     */
    public static <T> void exportExcel(ExportParams<T> exportParams, OutputStream out) {

        try {
            HSSFWorkbook workbook = new HSSFWorkbook();
            // 生成一个表格
            HSSFSheet sheet = workbook.createSheet();
            write2Sheet(sheet, exportParams, workbook);
            workbook.write(out);
        } catch (IOException e) {
            logger.error(e.toString(), e);
        }
    }


    /**
     * 将一个二维数组导出，一维是行，二维是行的cell值
     *
     * @param datalist
     * @param out
     */
    public static void exportExcel(String[][] datalist, OutputStream out) {
        try (HSSFWorkbook workbook = new HSSFWorkbook()) {
            // 生成一个表格
            HSSFSheet sheet = workbook.createSheet();

            for (int i = 0; i < datalist.length; i++) {
                String[] r = datalist[i];
                HSSFRow row = sheet.createRow(i);
                for (int j = 0; j < r.length; j++) {
                    HSSFCell cell = row.createCell(j);
                    //cell max length 32767
                    if (r[j].length() > 32767) {
                        r[j] = "--此字段过长(超过32767),已被截断--" + r[j];
                        r[j] = r[j].substring(0, 32766);
                    }
                    cell.setCellValue(r[j]);
                }
            }
            //自动列宽
            if (datalist.length > 0) {
                int colcount = datalist[0].length;
                for (int i = 0; i < colcount; i++) {
                    sheet.autoSizeColumn(i);
                }
            }
            workbook.write(out);
        } catch (IOException e) {
            logger.error(e.toString(), e);
        }
    }

    /**
     * 利用JAVA的反射机制，将放置在JAVA集合中并且符合一定条件的数据以EXCEL 的形式输出到指定IO设备上<br>
     * 用于多个sheet
     *
     * @param <T>
     * @param out 与输出设备关联的流对象
     */
    public static <T> void exportMultipleSheetExcel(ExportParams<T> exportParams, OutputStream out) {
        List<ExcelSheet<T>> sheets = exportParams.getSheets();
        Map<String, ExcelCellStyle> headerStyleMap = exportParams.getHeaderStyleMap();
        exportExcel(sheets, headerStyleMap, out);
    }

    /**
     * 利用JAVA的反射机制，将放置在JAVA集合中并且符合一定条件的数据以EXCEL 的形式输出到指定IO设备上<br>
     * 用于多个sheet
     *
     * @param <T>
     * @param sheets {@link ExcelSheet}的集合
     * @param out    与输出设备关联的流对象
     */
    public static <T> void exportExcel(List<ExcelSheet<T>> sheets, Map<String, ExcelCellStyle> headerStyleMap, OutputStream out) {
        if (CollectionUtils.isEmpty(sheets)) {
            return;
        }
        try (Workbook workbook = new HSSFWorkbook()) {

            for (ExcelSheet<T> sheet : sheets) {
                // 生成一个表格
                Sheet sheetItem = workbook.createSheet(sheet.getSheetName());
                ExportParams<T> exportParams = new ExportParams<>();
                exportParams.setHeaderMap(sheet.getHeaders());
                exportParams.setRowDatas(sheet.getDataset());
                exportParams.setHeaderStyleMap(headerStyleMap);
                write2Sheet(sheetItem, exportParams, workbook);
            }
            workbook.write(out);
        } catch (IOException e) {
            logger.error(e.toString(), e);
        }
    }

    /**
     * 把Excel的数据封装成voList
     *
     * @param inputStream excel输入流
     * @param logs        错误log集合
     * @return voList
     * @throws RuntimeException
     */
    @Deprecated
    public static <T> Collection<T> importExcel(ImportParams<T> importParams, InputStream inputStream,
                                                ExcelLogs logs) {
        return importExcel(importParams, inputStream, logs, null);
    }


    public static Collection importExcel(ExcelConfigurer excelConfigurer, InputStream inputStream,
                                         ExcelContext excelContext) {

        ImportParams importParams = new ImportParams();
        importParams.setImportType(excelConfigurer.getImportType());
        importParams.setColumnSpecifications(excelConfigurer.getColumnSpecification());
        importParams.setImportHeader(excelConfigurer.getImportHeader());

        CommonSpecification commonSpecification = new CommonSpecification();
        commonSpecification.setMobileNoAlias(excelConfigurer.getMobileNoAlias());
        commonSpecification.setStaffNameAlias(excelConfigurer.getStaffNameAlias());
        commonSpecification.setShowRowNum(excelConfigurer.getShowRowNum());
        commonSpecification.setRowNumKey(excelConfigurer.getRowNumKey());
        commonSpecification.setHeaderColumnJudge(excelConfigurer.getHeaderColumnJudge());
        commonSpecification.setAtLeastOneOrIgnoreRow(excelConfigurer.getAtLeastOneOrIgnoreRow());
        importParams.setCommonSpecification(commonSpecification);

        if (excelConfigurer instanceof Ihr360TemplateExcelConfiguration) {
            Ihr360TemplateExcelConfiguration ihr360TemplateExcelConfiguration = (Ihr360TemplateExcelConfiguration) excelConfigurer;
            commonSpecification.setTemplateHeaderRowNum(ihr360TemplateExcelConfiguration.getTemplateHeaderRowNum());
            commonSpecification.setTemplateDataBeginRowNum(ihr360TemplateExcelConfiguration.getTemplateDataBeginRowNum());
            commonSpecification.setTemplateHeaders(ihr360TemplateExcelConfiguration.getTemplateHeaders());
        } else {
            // todo
        }

        return importExcel(importParams, new BufferedInputStream(inputStream), excelContext.getLogs(), null);
    }

    /**
     * 历史版本，不建议使用，可使用  {@link #importExcel(ExcelConfigurer excelConfigurer, InputStream inputStream,ExcelContext excelContext)}
     *
     * @param importParams
     * @param inputStream
     * @param logs
     * @param excelContext
     * @param <T>
     * @return
     */
    @Deprecated
    public static <T> Collection<T> importExcel(ImportParams<T> importParams,
                                                InputStream inputStream,
                                                final ExcelLogs logs,
                                                final ExcelContext excelContext) {
        Class<T> clazz = importParams.getImportType();

        Sheet sheet = getSheetFromWorkbook(inputStream, logs);
        if (sheet == null) {
            return new ArrayList<>();
        }

        ExcelConfig excelConfig = clazz.getAnnotation(ExcelConfig.class);
        List<T> resultList = new ArrayList<>();
        List<ExcelRowLog> rowLogList = new ArrayList<>();


        Iterator<Row> rowIterator = sheet.rowIterator();
        // 从excel读取的表头 Map<title,index>
        Map<String, Integer> fileHeaderIndexMap = new LinkedHashMap<>();
        //最后的连续空行忽略
        List<ExcelRowLog> tempEmptyLogList = new ArrayList<>();

        CommonSpecification commonSpecification = importParams.getCommonSpecification();
        List<List<String>> headerJudgeList = Lists.newArrayList();
        if (commonSpecification != null) {
            headerJudgeList = commonSpecification.getHeaderColumnJudge();
            if (MapUtils.isNotEmpty(commonSpecification.getTemplateHeaders())) {
                fileHeaderIndexMap = commonSpecification.getTemplateHeaders();
            }
        }

        boolean containsHeader = false;
        Row headerRow = null;
        while (rowIterator.hasNext()) {

            List<ExcelLogItem> rowLogs = new ArrayList<>();
            Row row = rowIterator.next();
            boolean isBlankRow = checkBlankRow(excelConfig, row);
            if (MapUtils.isNotEmpty(fileHeaderIndexMap) && commonSpecification != null
                    && commonSpecification.getTemplateDataBeginRowNum() != null
                    && (!isBlankRow && row.getRowNum() < commonSpecification.getTemplateDataBeginRowNum())) {

                continue;
            }

            if ((row.getRowNum() == 0 || CollectionUtils.isNotEmpty(headerJudgeList)) && !containsHeader) {

                //隐藏行或空行
                if (row.getZeroHeight() || isBlankRow) {
                    rowLogs.add(ExcelLogItem.createExcelItem(ExcelLogType.FIRST_ROW_RULE, null));
                    rowLogList.add(new ExcelRowLog(rowLogs, row.getRowNum() + 1));
                    break;
                }
                fileHeaderIndexMap = convertRowToHeaderMap(row, false);
                headerRow = row;

                //表头判断
                containsHeader = judgeHeader(fileHeaderIndexMap, headerJudgeList);

                if (!containsHeader && CollectionUtils.isNotEmpty(headerJudgeList)) {
                    headerRow = null;
                    rowLogs.add(ExcelLogItem.createExcelItem(ExcelLogType.IGNORE_ROW, new String[]{row.getRowNum() + 1 + ""}));
                    rowLogList.add(new ExcelRowLog(rowLogs, row.getRowNum() + 1));
                }

                continue;
            }
            if (MapUtils.isEmpty(fileHeaderIndexMap)) {
                fileHeaderIndexMap = convertRowToHeaderMap(headerRow, true);
            }
            if (excelContext != null) {
                excelContext.setHeaderRowNum(headerRow.getRowNum());
            }


            //隐藏行
            if (row.getZeroHeight()) {
                boolean ignoreHiddenRows = excelConfig == null || excelConfig.ignoreHiddenRows();
                if (ignoreHiddenRows) {
                    rowLogs.add(ExcelLogItem.createExcelItem(ExcelLogType.HIDDEN_ROW, new String[]{row.getRowNum() + 1 + ""}));
                    rowLogList.add(new ExcelRowLog(rowLogs, row.getRowNum() + 1));
                    continue;
                }
            }
            //根据规则忽略行
            List<List<String>> atLeastHeaders = Lists.newArrayList();
            if (commonSpecification != null) {
                atLeastHeaders = commonSpecification.getAtLeastOneOrIgnoreRow();
            }
            if (CollectionUtils.isNotEmpty(atLeastHeaders)) {
                Set<String> headerSet = fileHeaderIndexMap.keySet();
                boolean contains = false;
                boolean isBreak = false;
                for (List<String> ailiasHeaders : atLeastHeaders) {
                    if (CollectionUtils.isEmpty(ailiasHeaders)) {
                        continue;
                    }
                    /*for (String ailiasHeader : ailiasHeaders) {
                        if (headerSet.contains(ailiasHeader)) {
                            Integer index = fileHeaderIndexMap.get(ailiasHeader);
                            Cell cell = row.getCell(index);
                            if (!isNullOrBlankStringCell(cell)) {
                                contains = true;
                                break;
                            }
                        }
                    }*/
                    //不区分大小写
                    for (String ailiasHeader : ailiasHeaders) {
                        for (String header : headerSet) {
                            String headerOld = header;
                            if (header.replace(" ", "").equalsIgnoreCase(ailiasHeader)) {
                                Integer index = fileHeaderIndexMap.get(headerOld);
                                Cell cell = row.getCell(index);
                                if (!isNullOrBlankStringCell(cell)) {
                                    contains = true;
                                    break;
                                }
                            }
                        }
                    }
                    if (contains) {
                        break;
                    }
                }
                if (!contains) {
                    rowLogs.add(ExcelLogItem.createExcelItem(ExcelLogType.IGNORE_ROW, new String[]{row.getRowNum() + 1 + ""}));
                    rowLogList.add(new ExcelRowLog(rowLogs, row.getRowNum() + 1));
                    continue;
                }
            }

            // 跳过空行,并记录日志
            if (isBlankRow) {
                rowLogs.add(ExcelLogItem.createExcelItem(ExcelLogType.BLANK_ROW, new String[]{row.getRowNum() + 1 + ""}));
                tempEmptyLogList.add(new ExcelRowLog(rowLogs, row.getRowNum() + 1));
                continue;
            } else {
                if (CollectionUtils.isNotEmpty(tempEmptyLogList)) {
                    rowLogList.addAll(tempEmptyLogList);
                    tempEmptyLogList.clear();
                }
            }

            //输出数据类型是Map时，简单将数据封装为Map<headerName,value>
            if (clazz == Map.class) {
                Map<String, Object> map = handleExcelRowToMap(fileHeaderIndexMap, row, rowLogs, importParams.getColumnSpecifications());
                if (CollectionUtils.isEmpty(rowLogs)) {
                    handleCommonSpecification(importParams, row, map);
                    resultList.add((T) map);
                } else {
                    rowLogList.add(new ExcelRowLog(rowLogs, row.getRowNum() + 1));
                }
            } else {

                T excelEntityVo = handleImportExcelRowToJavabean(importParams, fileHeaderIndexMap, rowLogs, row);
                if (CollectionUtils.isEmpty(rowLogs)) {
                    resultList.add(excelEntityVo);
                } else {
                    rowLogList.add(new ExcelRowLog(rowLogs, row.getRowNum() + 1));
                }
            }
        }
        if (headerRow == null && CollectionUtils.isNotEmpty(headerJudgeList)) {
            ExcelCommonLog commonLog = new ExcelCommonLog();
            logs.setExcelCommonLog(commonLog);
            List<String> headers = headerJudgeList.stream().map(header -> header.get(0)).collect(Collectors.toList());

            List<ExcelLogItem> excelLogItems = Lists.newArrayList();
            ExcelLogItem excelLogItem = new ExcelLogItem(ExcelLogType.HEADER_REQUIRED, headers.toArray());
            excelLogItems.add(excelLogItem);
            commonLog.setExcelLogItems(excelLogItems);
            return resultList;
        }

        if (CollectionUtils.isEmpty(resultList) && CollectionUtils.isEmpty(rowLogList)) {
            ExcelCommonLog commonLog = new ExcelCommonLog();
            logs.setExcelCommonLog(commonLog);
            List<ExcelLogItem> excelLogItems = Lists.newArrayList();
            ExcelLogItem excelLogItem = new ExcelLogItem(ExcelLogType.EXCEL_COMMON_NO_DATA, null);
            excelLogItems.add(excelLogItem);
            commonLog.setExcelLogItems(excelLogItems);
            return resultList;
        }


        logs.setRowLogList(rowLogList);
        return resultList;
    }


    /**
     * 获取Excel第一行数据
     * 不忽略空行与隐藏行
     *
     * @param inputStream
     * @param logs
     * @return
     */
    public static Map<String, Integer> importGetFirstLineHeaderToMap(InputStream inputStream, ExcelLogs logs) {

        Sheet sheet = getSheetFromWorkbook(inputStream, logs);
        if (sheet == null) {
            return Maps.newLinkedHashMap();
        }
        Iterator<Row> rowIterator = sheet.rowIterator();
        // 从excel读取的表头 Map<title,index>
        Map<String, Integer> fileHeaderIndexMap = Maps.newLinkedHashMap();

        if (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            if (row.getRowNum() == 0) {
                fileHeaderIndexMap = convertRowToHeaderMap(row, false);
            }
        }
        return fileHeaderIndexMap;
    }

    /**
     * 导入获取真实数据所有条数
     *
     * @return
     */
    public static Integer importGetDataNum(InputStream inputStream, ExcelLogs logs) {
        Sheet sheet = getSheetFromWorkbook(inputStream, logs);

        Integer dataNum = 0;

        Iterator<Row> rowIterator = sheet.rowIterator();
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            if (!checkBlankRow(null, row) && !row.getZeroHeight()) {
                dataNum++;
            }
        }
        return dataNum;
    }

    /**
     * 根据日志生成带注释的表格
     *
     * @param rowLogList
     * @param removeRight  删除正确数据
     * @param excelContext
     * @return
     */
    public static byte[] generateCommentFile(InputStream inputStream, List<ExcelRowLog> rowLogList, boolean removeRight, ExcelContext excelContext) {

        if (CollectionUtils.isEmpty(rowLogList)) {
            throw new ExcelException("日志为空，无法生成注释文件！");
        }
        Map<Integer, List<ExcelLogItem>> rowLogMap = Maps.newHashMap();
        rowLogList.forEach(rowLog -> {
            rowLogMap.put(rowLog.getRowNum(), rowLog.getExcelLogItems());
        });
        Sheet sheet = null;
        try (Workbook workBook = WorkbookFactory.create(new BufferedInputStream(inputStream));) {
            sheet = workBook.getSheetAt(0);
            ByteArrayOutputStream out = new ByteArrayOutputStream();

            Iterator<Row> rowIterator = sheet.rowIterator();
            Set<Row> toRevomeRows = Sets.newHashSet();
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                List<ExcelLogItem> logItems = rowLogMap.get(row.getRowNum() + 1);

                if (CollectionUtils.isEmpty(logItems)) {
                    if (removeRight && excelContext != null && excelContext.getHeaderRowNum() != row.getRowNum()) {
                        toRevomeRows.add(row);
                    }
                    continue;
                }

                logItems = logItems.stream().filter(l -> l.getColNum() != null).collect(Collectors.toList());

                for (ExcelLogItem logItem : logItems) {
                    String commentStr = logItem.getMessage();
                    Integer columnIndex = logItem.getColNum();
                    Cell cell = row.getCell(columnIndex);
                    if (cell == null) {
                        cell = row.createCell(columnIndex);
                    }
                    CellComment cellComment = CellComment.createCellComment(new int[]{255, 125, 1023, 150, 0, 0, 2, 2}, null, commentStr, false);


                    Drawing patr = sheet.createDrawingPatriarch();
                    int[] params = cellComment.getAnchorParams();
                    ClientAnchor anchor = patr.createAnchor(params[0], params[1], params[2], params[3], params[4], params[5], params[6], params[7]);
                    Comment comment = patr.createCellComment(anchor);
                    if (StringUtils.isNotBlank(cellComment.getContentString())) {
                        try {
                            comment.setString(new HSSFRichTextString(cellComment.getContentString()));
                        } catch (IllegalArgumentException exception) {
                            comment.setString(new XSSFRichTextString(cellComment.getContentString()));
                        }
                    }
                    if (StringUtils.isNotBlank(cellComment.getAuthor())) {
                        comment.setAuthor(cellComment.getAuthor());
                    }
                    comment.setVisible(cellComment.isVisible());
                    cell.setCellComment(comment);
                }
            }
            if (CollectionUtils.isNotEmpty(toRevomeRows)) {
                for (Row row : toRevomeRows) {
                    sheet.getRow(row.getRowNum()).setZeroHeight(true);
                }
            }

            workBook.write(out);
            return out.toByteArray();

        } catch (InvalidFormatException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        if (sheet == null) {
            throw new ExcelException("获取文件失败！");
        }
        return null;
    }
}
